﻿/*
Post-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.		
 Use SQLCMD syntax to include a file in the post-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/

-- Add BizTalk Server Groups to Database Roles
--:setvar BtsAdminGroup "CCIDEV\BizTalk Server Administrators"
CREATE USER [$(BtsAdminGroup)] FOR LOGIN [$(BtsAdminGroup)];
GO

EXECUTE sp_addrolemember @rolename = N'ESBPortalAdmin', @membername = [$(BtsAdminGroup)];
GO

GRANT CONNECT TO [$(BtsAdminGroup)] AS [dbo];
GO

--:setvar BtsUserGroup "CCIDEV\BizTalk Application Users"
CREATE USER [$(BtsUserGroup)] FOR LOGIN [$(BtsUserGroup)];
GO

EXECUTE sp_addrolemember @rolename = N'ESBPortal', @membername = [$(BtsUserGroup)];
GO

GRANT CONNECT TO [$(BtsUserGroup)] AS [dbo];
GO

Alter TABLE EsbExceptionDb.dbo.ContextProperty ALTER COLUMN Value varchar(MAX)

GO

--USE [EsbExceptionDb]
--GO
/****** Object:  StoredProcedure [dbo].[usp_insert_ContextProperty]    Script Date: 08/01/2011 15:13:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_insert_ContextProperty]
(
    @MessageID				uniqueidentifier 
    ,@Name					varchar(256) 
    ,@Value					varchar(MAX) 
    ,@Type					varchar(256) 
	,@Debug					bit			= 1
)
AS

/****************************************************************************
Name: usp_insert_ContextProperty.sql
Description: Inserts a message's context property into the ContextProperty
	table.
*****************************************************************************/
SET NOCOUNT ON;

-- Bootstrap
DECLARE @DebugPrefix       varchar(10);
DECLARE @ErrorCode         int;

SET @ErrorCode          = 0;
SET @DebugPrefix        = '>>> DEBUG: ';


-- Test harness
/*
DECLARE @ContextPropertyID		uniqueidentifier 
DECLARE @MessageID				uniqueidentifier 
DECLARE @Name					varchar(50) 
DECLARE @Value					varchar(50) 
DECLARE @Type					varchar(50) 
DECLARE @InsertedDate			datetime 
DECLARE @InsertedBy				varchar(50) 
DECLARE @ModifiedDate			datetime	= NULL
DECLARE @ModifiedBy				varchar(50) = NULL
		@Debug					bit			= 1
SELECT
	 @MessageID			= NEWID()		
	,@FaultID			= NEWID()		
	,@MessageName		= 'test'	
	,@RoutingUrl		= 'test_url'	
	,@MessageData	
	,@InsertedDate	
	,@InsertedBy	
	,@ModifiedDate	
	,@ModifiedBy
	,@Debug                 = 1;

*/

-- Print parameters
/*IF (@Debug > 0)
BEGIN
	PRINT @DebugPrefix + '@Name = ' + @Name;
	PRINT @DebugPrefix + '@Value = ' + @Value;
	PRINT @DebugPrefix + '@Type = ' + @Type;
	--PRINT @DebugPrefix + '@ContextPropertyID = ' + CAST(@ContextPropertyID AS varchar);
    PRINT @DebugPrefix + '@MessageID = ' + CAST(@MessageID AS varchar);
END;*/


INSERT INTO 
			[dbo].[ContextProperty]
			(
				[MessageID]
				,[Name]
				,[Value]
				,[Type]
			)

VALUES
			(
				 @MessageID			
				,@Name				
				,@Value				
				,@Type			
			)	

-- CHECK FOR ERROR
SET @ErrorCode = @@Error;

IF (@ErrorCode <> 0) GOTO ERROR_HANDLER;

RETURN 0;

-- Handles errors.
ERROR_HANDLER:
IF (@ErrorCode <> 0)
BEGIN
    IF (@Debug > 0) 
		BEGIN
			PRINT @DebugPrefix + 'Error Code = ' + CAST(@ErrorCode AS varchar(10)) + ' occurred.';
			RETURN @ErrorCode
		END

    -- Don't return any evidence of what specific error occurred, i.e. 
    -- we want to ward off potential hackers.
    RETURN 1;
END;

SET NOCOUNT OFF;


-- INSERT DEFAULT DATA

--	Action Types
--INSERT INTO [dbo].[ActionType] ([ActionTypeID],[ActionName])
--     VALUES ('9E97D8A1-CEE0-46E7-A642-04D469550311','UnsuccessfulResubmit')
--GO
--INSERT INTO [dbo].[ActionType] ([ActionTypeID],[ActionName])
--     VALUES ('523EB229-64E4-4D7F-A624-0D57DA288EBE','Save')
--GO
--INSERT INTO [dbo].[ActionType] ([ActionTypeID] ,[ActionName])
--     VALUES ('1CE1382D-1287-4FFA-A7A7-5F8439FD3816' ,'Create')
--GO
--INSERT INTO [dbo].[ActionType] ([ActionTypeID] ,[ActionName])
--     VALUES ('26514D64-DCA4-4692-A06D-8AE3AC3D8835' ,'SuccessfulResubmit')
--GO
--INSERT INTO [dbo].[ActionType] ([ActionTypeID],[ActionName])
--     VALUES ('AB4790A4-5081-497D-A99D-9DFD860D3D11' ,'Import')
--GO
--INSERT INTO [dbo].[ActionType] ([ActionTypeID],[ActionName])
--     VALUES ('F380BCA4-8F27-47A1-80C6-A16171002A95' ,'Delete')
--GO
--INSERT INTO [dbo].[ActionType] ([ActionTypeID],[ActionName])
--     VALUES ('E180A525-451C-4680-8739-C4DF54CA8391','Export')
--GO

-- Configurations
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('34538AB1-4AE4-4553-8C9F-09EFB7922941','IsEmailEnabled','true','Enable or disable email process',NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('03B6A826-F887-4234-89B8-1F8AEFCF3879','XsltPath','E:\hisctfs\HISC.ESB\HISC.ESB.Portal\Services\ESB.AlertService\Templates\EmailTransformation.xslt','Absolute path for XSLT file which is used to generate html emal body',NULL,NULL)
     --VALUES ('03B6A826-F887-4234-89B8-1F8AEFCF3879','XsltPath',[$(AlertEmailTemplatePath)],'Absolute path for XSLT file which is used to generate html emal body',NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('AB87A51F-10E5-4BD7-A673-41A592F86BD5','Sender','DoNotReply@homeinsteadinc.com','Senders email address',NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('077155ED-F441-4E90-9806-4717BBA42FDD','EmailServer','ho-smtp-1.hisc.local','Name of Email Server',NULL,NULL)
     --VALUES ('077155ED-F441-4E90-9806-4717BBA42FDD','EmailServer',[$(AlertSMTP)],'Name of Email Server',NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('A7622A27-55BD-4671-8412-550E4B77932A','LdapRoot','LDAP://domaincontroller.domain.com/DC=domain, DC=com','Root of active directory from which search starts',NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('5714E3BF-E8A4-4B86-BBDC-6102C64B65A2','IsQueueEnabled','0','Enable or disable queue process',NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('F68BD53A-DCCB-498D-BC00-8C687FDD8172','ADCacheInterval','2','Cache will be recycled after the time elapsed',NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('77C61678-9DC6-4DAB-A48D-92EAF53204BA','AuditSuccessfulResubmit','1',NULL,NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('F7D596B9-DE40-42E3-91F1-93FB90CD25DD','ConfigurationCacheInterval','2','Configuration cache will be recycled after the time elapsed in minutes.',NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('B17BFED1-1C78-4A14-9B95-9434C9FF0FC1','QueueBatchSize','5','Number of records processed for queue at one time',NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('A4F0B9E2-0163-494D-833A-95DF1774CA29','EmailBatchSize','5','Number of records processed for email',NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('A8B8D491-06E6-4F48-875E-A27FAE41BD13','EmailInterval','60000','Email process sleep in milliseconds',NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('0576713F-B1C2-41E2-A5AC-DE5D23255384','AuditSave','1',NULL,NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('6FE47426-9643-4F32-91B8-DF1454D1944E','AuditUnsuccessfulResubmit','1',NULL,NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('68E94248-FD2C-4CB3-B1EB-E71997590420','QueueInterval','60000','Queue process sleep in milliseconds',NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('2D0758CA-E743-4864-A681-8FCBE9F3E0E7','AuditSSO','1',NULL,NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('E698E874-1B72-4126-AD24-C2DE51CDFC13','AuditUserAccessPolicy','1',NULL,NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('F2CB7036-58B4-428C-A713-09DB0E9A34C5','AuditItineraryStatusChange','1',NULL,NULL,NULL)
GO

-- Batch Resubmission Settings
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('A01F2DDF-BFCF-47A6-9A0B-7857CB6EAF28','BatchResubmitNumberOfMessages','100','Number of messages for the Batch Resubmission service to process at a time',NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('CEC36228-7101-411A-BFCB-19CF346FD4BC','BatchResubmitGenericSubmissionURI','http://localhost/ESB.ItineraryServices.WCF/ProcessItinerary.svc','Location of generic onramp to use when submitting messages to generic receive port.',NULL,NULL)
     --VALUES ('CEC36228-7101-411A-BFCB-19CF346FD4BC','BatchResubmitGenericSubmissionURI',[$(BatchResubmitGenericURL)],'Location of generic onramp to use when submitting messages to generic receive port.',NULL,NULL)
GO
INSERT INTO [dbo].[Configuration] ([ConfigurationID],[Name],[Value],[Description],[ModifiedDate],[ModifiedBy])
     VALUES ('85084369-021E-47B8-AA55-0C152F2BA9D6','BatchResubmitPollingInterval','30','Number of seconds between queries the Batch Resubmission service will execute looking for messages to resubmit.',NULL,NULL)
GO